CREATE PROCEDURE [dbo].[asi_CreateInstitute]
    @contactKey uniqueidentifier,
    @id nvarchar(20),
    @instituteName nvarchar(80),
    @companysort nvarchar(110),
    @statusCode nvarchar(5),
    @noSolicitationFlag bit,
    @instituteTypeKey uniqueidentifier,
    @createdByUserKey uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @accessKey uniqueidentifier
    DECLARE @ownerAccessKey uniqueidentifier
    DECLARE @groupKey uniqueidentifier
    DECLARE @groupTypeKey uniqueidentifier
    DECLARE @systemEntityKey uniqueidentifier
    DECLARE @registryKey uniqueidentifier
    DECLARE @now datetime
    DECLARE @isInvitationOnly bit
    DECLARE @isSimpleGroup bit
    DECLARE @inheritRolesFlag bit
    DECLARE @isSingleRole bit
    DECLARE @instituteTypeName nvarchar(50)

    DECLARE @adminRoleKey uniqueidentifier
    DECLARE @everyoneRoleKey uniqueidentifier
    DECLARE @fullControlAccessKey uniqueidentifier

    SELECT @adminRoleKey = ParameterValue FROM SystemConfig
        WHERE ParameterName = 'Security.Token.AdministratorsRoleKey'
    SELECT @everyoneRoleKey = ParameterValue FROM SystemConfig
        WHERE ParameterName = 'Security.Token.EveryoneRoleKey'
    SELECT @fullControlAccessKey = ParameterValue FROM SystemConfig
        WHERE ParameterName = 'Security.Token.EveryoneFullControlAccessKey'

    SELECT @instituteTypeName = ContactTypeDesc FROM ContactTypeRef
        WHERE ContactTypeKey = @instituteTypeKey

    SET @now = getdate()
    SET @accessKey = newid()
    SET @ownerAccessKey = newid()
    SET @groupKey = newid()

    -- Create UniformRegistry entry for institute group
    SELECT @registryKey = ComponentKey FROM ComponentRegistry
          WHERE Name = 'Group' and InterfaceName = 'BusinessController'
    INSERT UniformRegistry (UniformKey, ComponentKey)
    VALUES (@groupKey, @registryKey)

    -- Create the group access set
    INSERT    AccessMain (AccessKey, AccessScope, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn)
    VALUES    (@accessKey, 'Local', @createdByUserKey, @now, @createdByUserKey, @now)

    -- Grant Administrators role full control permission
    INSERT    AccessItem (AccessKey, Grantee, Permission, RoleKey)
    VALUES  (@accessKey, @adminRoleKey, 1, @adminRoleKey)

    -- Grant Everyone role read permission
    INSERT    AccessItem (AccessKey, Grantee, Permission, RoleKey)
    VALUES  (@accessKey, @everyoneRoleKey, 2, @everyoneRoleKey)
    
    -- Create the group owner access set (for items owned by the group)
    INSERT    AccessMain (AccessKey, AccessScope, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn)
    VALUES    (@ownerAccessKey, 'Local', @createdByUserKey, @now, @createdByUserKey, @now)

    -- Grant Administrators role full control permission
    INSERT    AccessItem (AccessKey, Grantee, Permission, RoleKey)
    VALUES  (@ownerAccessKey, @adminRoleKey, 1, @adminRoleKey)

    -- Grant Everyone role read permission
    INSERT    AccessItem (AccessKey, Grantee, Permission, RoleKey)
    VALUES  (@ownerAccessKey, @everyoneRoleKey, 2, @everyoneRoleKey)
    
    -- Create a new group for the institute
    SELECT @groupTypeKey = [GroupTypeRef].GroupTypeKey,
           @isInvitationOnly = IsInvitationOnly,
           @isSimpleGroup = IsSimpleGroup,
           @inheritRolesFlag = InheritRolesFlag,
           @isSingleRole = IsSingleRole
    FROM [GroupTypeRef]
    INNER JOIN [ContactTypeRef]
    ON [GroupTypeRef].[GroupTypeKey] = [ContactTypeRef].[GroupTypeKey]
    AND [ContactTypeRef].[ContactTypeKey] = @instituteTypeKey

    SELECT @systemEntityKey = SystemEntityKey FROM [SystemEntity]
        WHERE [IsDefault] = 1 AND [SystemKeyword] = 'Organization'    

    INSERT INTO [GroupMain] (GroupKey, [Name], Description,
                             CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn,
                             IsSystem, IsAutoGenerated, GroupTypeKey, AccessKey, SystemEntityKey, IsInvitationOnly,
                             GroupStatusCode, IsSimpleGroup, InheritRolesFlag, IsSingleRole, OwnerAccessKey)
    VALUES (@groupKey, @instituteName, 'Group for ' + @instituteName + ' ' + @instituteTypeName,
            @createdByUserKey, @now, @createdByUserKey, @now,
            0, 1, @groupTypeKey, @accessKey, @systemEntityKey, @isInvitationOnly, 'A', @isSimpleGroup,
            @inheritRolesFlag, @isSingleRole, @ownerAccessKey)

    -- Give the group full control to items protected by its owner access key
    INSERT    AccessItem (AccessKey, Grantee, Permission, GroupKey)
    VALUES  (@ownerAccessKey, @groupKey, 1, @groupKey)

    -- Create UniformRegistry entry for institute itself
    SELECT @registryKey = ComponentKey FROM ComponentRegistry
          WHERE Name = 'Institute' and InterfaceName = 'BusinessController'
    INSERT UniformRegistry (UniformKey, ComponentKey)
    VALUES (@contactKey, @registryKey)

    -- Create Contact entry
    INSERT INTO [ContactMain]
           ([ContactKey], [ContactStatusCode], [FullName], [SortName], [IsInstitute], [NoSolicitationFlag],
            [SyncContactID], [ID], [IsIDEditable], [CreatedByUserKey], [CreatedOn], [UpdatedByUserKey], [UpdatedOn],
             [PreferredAddressCategoryCode], [IsSortNameOverridden], [AccessKey], [TextOnlyEmailFlag], [ContactTypeKey], [OptOutFlag])
    VALUES (@contactKey, @statusCode, @instituteName, @companysort, 1, @noSolicitationFlag,
            @id, 'C' + @id, 0, @createdByUserKey, @now, @createdByUserKey, @now, '1',
            0, @fullControlAccessKey, 0, @instituteTypeKey, 0)

    -- Create the new Institute record
    INSERT INTO [Institute] (ContactKey, InstituteName, InstituteGroupKey)
    VALUES (@contactKey, @instituteName, @groupKey)

END

GO